package k.dao.base;

import k.HelperClazz;
import k.dto.Emp;
import k.dto.Event;
import k.dto.Org;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import java.util.List;

/**
 * Created by 隔壁老王 on 2018/4/11.
 */
@Component
public class EmpDao {
    private String sql;
    @Autowired
    BaseDao baseDao;

    public Emp getEmp(String login_name, String password) {
        sql = " SELECT e.emp_id,e.emp_name,e.phone,e.login_name,e.`password`,o.org_id,o.org_name,GROUP_CONCAT(r.role_id) role_id " +
                " FROM EMP e LEFT JOIN ORG o ON e.org_id=o.org_id LEFT JOIN emp_role r ON e.emp_id=r.emp_id" +
                " WHERE e.login_name='"+login_name +"' and e.password='"+password
                +"' GROUP BY e.emp_id";
        Emp emp =baseDao.query(sql,Emp.class).get(0);
        return emp;
    }

    public Emp getEmpById(String emp_id) {
        sql = " SELECT e.emp_id,e.emp_name,e.phone,e.login_name,e.`password`,o.org_id,o.org_name,GROUP_CONCAT(r.role_id) role_id " +
                " FROM EMP e LEFT JOIN ORG o ON e.org_id=o.org_id LEFT JOIN emp_role r ON e.emp_id=r.emp_id" +
                " WHERE e.emp_id=?"
                +" GROUP BY e.emp_id";
        Emp emp =baseDao.queryById(sql,Emp.class,emp_id);
        return emp;
    }
    public List<Emp> getGridOwnerList(String orgId) {
        sql = " SELECT e.emp_id,e.emp_name,e.phone,e.login_name,e.`password`,o.org_id,o.org_name,GROUP_CONCAT(r.role_id) role_id " +
                " FROM EMP e LEFT JOIN ORG o ON e.org_id=o.org_id LEFT JOIN emp_role r ON e.emp_id=r.emp_id" +
                " WHERE e.org_id="+orgId+" and r.role_id =4"
                +" GROUP BY e.emp_id";
        List<Emp> emp =baseDao.query(sql,Emp.class);
        return emp;
    }
    public Emp getEmpByName(String emp_name) {
        sql = " SELECT e.emp_id,e.emp_name,e.phone,e.login_name,e.`password`,o.org_id,o.org_name,GROUP_CONCAT(r.role_id) role_id " +
                " FROM EMP e LEFT JOIN ORG o ON e.org_id=o.org_id LEFT JOIN emp_role r ON e.emp_id=r.emp_id" +
                " WHERE e.emp_name=?"
                +" GROUP BY e.emp_id";
        Emp emp =baseDao.queryById(sql,Emp.class,emp_name);
        return emp;
    }
    public List<Emp> findALL(){
        sql ="SELECT * FROM `emp`   LEFT JOIN org  using(org_id)";
        return baseDao.query(sql,Emp.class);
    }
    /**
     * 根据当前登陆人ID查询人员信息
     * @param emp_id
     * @return
     */
    public Emp queryByEmp(String emp_id) {
        String sql="select * from emp where emp_id=?";
        Emp emp=baseDao.queryById(sql, Emp.class, new Object[]{emp_id});
        return emp;
    }

    /**
     * 根据orgID查询部门信息
     * @param orgId
     * @return
     */
    public Org SysOrgFindById(String orgId) {
        String sql="select * from org where org_id=?";
        Org org=baseDao.queryById(sql, Org.class, new Object[]{orgId});
        return org;
    }
    /**
     * Created by 王玉涛
     * 根据角色和部门查询empid并直接拼串
     *
     * @Param
     * @date 2017/7/2 17:31
     */
    public   String findSysEmpByOrgIdAndRole_id(String orgId, Integer roleId){
        String sql ="select GROUP_CONCAT(e.emp_id) as empIds from Emp e LEFT JOIN Emp_role er on e.emp_id = er.emp_id where e.org_id='"+orgId+"' AND er.role_id ="+roleId;
        return baseDao.getKVVals(sql);
    }
    public List<Emp> getEmps  (String orgId) {
        sql = " SELECT e.emp_id,e.emp_name,e.phone,e.login_name,e.`password`,o.org_id,o.org_name,GROUP_CONCAT(r.role_id) role_id " +
                " FROM EMP e LEFT JOIN ORG o ON e.org_id=o.org_id LEFT JOIN emp_role r ON e.emp_id=r.emp_id" +
                " WHERE e.org_id='"+orgId +"' and r.role_id='"+3
                +"' GROUP BY e.emp_id";
        List<Emp> emp =baseDao.query(sql,Emp.class) ;
        return emp;
    }

    public void insertEmp(Emp emp){
        sql = "INSERT INTO `bitserver`.`emp` (`emp_id`, `emp_name`, `phone`, `password`, `login_name`, `org_id`) VALUES (:emp_id, :emp_name, :phone, :password, :login_name, :org_id)";
        long emp_id = baseDao.insert(sql.toString(), emp);
    }

    public void updateEmp(Emp emp){
        sql = "UPDATE `bitserver`.`emp` SET  `emp_name`=:emp_name, `phone`=:phone, `password`=:password, `login_name`=:login_name, `org_id`=:org_id WHERE (`emp_id`=:emp_id)";
        baseDao.update(sql.toString(),emp);
    }

    public void deleteEmp(String empId){
        sql = "delete from emp  where emp_id = ? " ;
        Integer n = baseDao.delete(sql,new Object[]{empId});
    }
}
